using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
namespace CocaCola_Inventory_System.Forms
{
    public partial class Transfer : Form
    {
        public Transfer()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OdbcConnection con = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=egcaid02;Port=3306;Database=inventorydb;User=root; Password=kym07;Option=3;");
            OdbcCommand com = new OdbcCommand();
            com.Connection = con;

            DataSet ds = new DataSet();
            DialogResult res = openFileDialog1.ShowDialog();
            if (res != DialogResult.OK)
                return;
            try
            {
                ds.ReadXml(openFileDialog1.FileName);
            }
            catch(Exception x)
            {
                MessageBox.Show(x.Message);
                return;
            }
            Insert ins = new Insert(con);
            con.Open();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                com.CommandText = " select count(*) from users where name like '" + ds.Tables[0].Rows[i][5].ToString()+"'";
                if (Convert.ToInt32(com.ExecuteScalar()) == 1)
                { 
                com.CommandText = "select id from users where name like '" + ds.Tables[0].Rows[i][5].ToString()+"'";
                int user = Convert.ToInt32(com.ExecuteScalar());
                ins.item("", ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][4].ToString(), ds.Tables[0].Rows[i][2].ToString(),ds.Tables[0].Rows[i][0].ToString(), 0, 0, 0, DateTime.Now, 3, DateTime.Now, 7, user, 5,5, new object[0], new object[0],1,DateTime.Now,DateTime.MaxValue,false,1);

                }
                else
                {
                    ins.item(ds.Tables[0].Rows[i][5].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][4].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][0].ToString(), 0, 0, 0, DateTime.Now, 3, DateTime.Now, 7, 1361, 5,5, new object[0], new object[0], 1, DateTime.Now, DateTime.MaxValue,false,1);

                }




            }
           // con.Close();
            MessageBox.Show("Success");

        }
    }
}